﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.Utils.Menu;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid;
using ERP_RORZE_SYSTEM;
using ERP_RORZE_TOOL.Common;
using ERP_RORZE_TOOL.Tool.BCThoigianlamviecthang;

namespace ERP_RORZE_TOOL.Tool.BaoCaoKeHoach
{
    public partial class ucBaoCaoKeHoach : DevExpress.XtraEditors.XtraUserControl
    {
        DXPopupMenu menu;
        private DateTime date;
        private int ReportId;
        public ucBaoCaoKeHoach()
        {
            InitializeComponent();
            InitPopupMenu();
            BindDataItemList();
            BindDataReportList();
            this.dropbtExport.DropDownControl = menu;
            this.lblFormName.Text = ReportConstants.FormName;
        }

        private void cmbReportList_SelectionChangeCommitted(object sender, EventArgs e)
        {
            ReportConstants.Item item = (ReportConstants.Item)cmbReportList.SelectedItem;
            cmbData.SelectedItem = cmbData.Items[0];
            if (item.Value != -1)
            {
                if (item.Value == ReportConstants.ReportId.PWTSUpdate)
                {
                    lbDate.Visible = true;
                    dateEdit1.Visible = true;
                    btView.Visible = true;
                }
                else
                {
                    lbDate.Visible = false;
                    dateEdit1.Visible = false;
                    btView.Visible = false;
                }
                gridView1.Columns.Clear();
                panelBottom.Visible = false;
                this.ReportId = item.Value;
                BindDataToGridview(item.Value);
            }
        }

        private void btView_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            if (dateEdit1.EditValue == null || dateEdit1.EditValue == "")
            {
                MessageBox.Show(ReportConstants.Message.DateIsNotNull, "Message", MessageBoxButtons.OK,
                                        MessageBoxIcon.Error);
                dateEdit1.Focus();
            }
            else
            {
                switch (ReportId)
                {
                    case ReportConstants.ReportId.PWTSUpdate:
                        date = DateTime.Parse(dateEdit1.EditValue.ToString());
                        var paramUpdate = new SqlParameter[]
                        {
						    BaseDAO.CreateParameter("@Date", SqlDbType.DateTime, date)
                        };
                        dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.PWTSUpdate, paramUpdate);

                        gridView1.ViewCaption = ReportConstants.ReportName.PWTSUpdate;


                        break;
                    default:
                        break;
                }
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }



        }

        private void gridView1_CustomDrawEmptyForeground_1(object sender, DevExpress.XtraGrid.Views.Base.CustomDrawEventArgs e)
        {
            if (gridView1.RowCount > 0)
                return;

            StringFormat drawFormat = new StringFormat();

            drawFormat.Alignment = drawFormat.LineAlignment = StringAlignment.Center;

            e.Graphics.DrawString("No items exist in this view", e.Appearance.Font, SystemBrushes.ControlDark, new RectangleF(e.Bounds.X, e.Bounds.Y, e.Bounds.Width, e.Bounds.Height), drawFormat);

        }

        private void cmbData_SelectionChangeCommitted(object sender, EventArgs e)
        {
            ReportConstants.Item item = (ReportConstants.Item)cmbData.SelectedItem;
            cmbReportList.SelectedItem = cmbReportList.Items[0];
            if (item.Value != -1)
            {
                if (item.Value == ReportConstants.ReportId.PWTSUpdate)
                {
                    lbDate.Visible = true;
                    dateEdit1.Visible = true;
                    btView.Visible = true;
                }
                else
                {
                    lbDate.Visible = false;
                    dateEdit1.Visible = false;
                    btView.Visible = false;
                }

                gridView1.Columns.Clear();
                panelBottom.Visible = true;
                this.ReportId = item.Value;
                BindDataToGridview(item.Value);
            }
        }

        private void BindDataReportList()
        {
            cmbReportList.Items.Add(new ReportConstants.Item("Select..."));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.PWTSDelete, ReportConstants.ReportId.PWTSDelete));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.PWTSUpdate, ReportConstants.ReportId.PWTSUpdate));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.QBCBieuThoigianchaymayNgayVN, ReportConstants.ReportId.QBCBieuThoigianchaymayNgayVN));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.QBCBieuthoigianchaymayNgayJP, ReportConstants.ReportId.QBCBieuthoigianchaymayNgayJP));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.QTGCMCross_CrosstabVN, ReportConstants.ReportId.QTGCMCross_CrosstabVN));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.QTGCMCross_CrosstabJP, ReportConstants.ReportId.QTGCMCross_CrosstabJP));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.QBChangNG, ReportConstants.ReportId.QBChangNG));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.Qketquagiaconghangloat, ReportConstants.ReportId.Qketquagiaconghangloat));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.Qketquachaythu, ReportConstants.ReportId.Qketquachaythu));

            cmbReportList.SelectedItem = cmbReportList.Items[0];

        }

        private void BindDataItemList()
        {
            cmbData.Items.Add(new ReportConstants.Item("Select..."));
            cmbData.Items.Add(new ReportConstants.Item(ReportConstants.Table.Shift, ReportConstants.ReportId.Shift));
            cmbData.Items.Add(new ReportConstants.Item(ReportConstants.Table.WorkName, ReportConstants.ReportId.WorkName));

            cmbData.SelectedItem = cmbData.Items[0];

        }

        private void BindDataToGridview(int reportId)
        {
            DataTable dt = new DataTable();
            gridView1.Columns.Clear();

            switch (reportId)
            {
                case ReportConstants.ReportId.PWTSDelete:
                    gridView1.OptionsBehavior.Editable = false;
                    if (MessageBox.Show("Bạn muốn xóa dữ liệu?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.PWTSDelete);
                    }
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.PWTSUpdateGetAll);
                    gridView1.ViewCaption = ReportConstants.ReportName.CurrentData;
                    break;

                case ReportConstants.ReportId.PWTSUpdate:
                    gridView1.OptionsBehavior.Editable = false;
                    btView.Text = "Update";
                    dateEdit1.Focus();
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.PWTSUpdateGetAll);
                    gridView1.ViewCaption = ReportConstants.ReportName.CurrentData;
                    break;

                case ReportConstants.ReportId.QBCBieuThoigianchaymayNgayVN:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.QBCBieuThoigianchaymayNgayVN);
                    gridView1.ViewCaption = ReportConstants.ReportName.QBCBieuThoigianchaymayNgayVN;
                    break;

                case ReportConstants.ReportId.QBCBieuthoigianchaymayNgayJP:

                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.QBCBieuthoigianchaymayNgayJP);
                    gridView1.ViewCaption = ReportConstants.ReportName.QBCBieuthoigianchaymayNgayJP;
                    break;

                case ReportConstants.ReportId.QTGCMCross_CrosstabVN:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.QTGCMCross_CrosstabVN);
                    gridView1.ViewCaption = ReportConstants.ReportName.QTGCMCross_CrosstabVN;
                    break;

                case ReportConstants.ReportId.QTGCMCross_CrosstabJP:

                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.QTGCMCross_CrosstabJP);
                    gridView1.ViewCaption = ReportConstants.ReportName.QTGCMCross_CrosstabJP;
                    break;

                case ReportConstants.ReportId.QBChangNG:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.QBChangNG);
                    gridView1.ViewCaption = ReportConstants.ReportName.QBChangNG;
                    break;
                case ReportConstants.ReportId.Qketquagiaconghangloat:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.Qketquagiaconghangloat);
                    gridView1.ViewCaption = ReportConstants.ReportName.Qketquagiaconghangloat;
                    break;
                case ReportConstants.ReportId.Qketquachaythu:

                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.Qketquachaythu);
                    gridView1.ViewCaption = ReportConstants.ReportName.Qketquachaythu;
                    break;

                //////////////////////////Data items//////////////////////////////////////////////////////////////////////////////////
                case ReportConstants.ReportId.Shift:

                    gridView1.OptionsBehavior.Editable = true;
                    GridColumn colIdShift = new GridColumn()
                        {
                            Caption = String.Format("ID"),
                            FieldName = "ID",
                        };
                    gridView1.Columns.Add(colIdShift);
                    colIdShift.VisibleIndex = -1;

                    GridColumn colShift = new GridColumn()
                        {
                            Caption = String.Format("Shift"),
                            FieldName = "Shift",
                        };
                    gridView1.Columns.Add(colShift);
                    colShift.VisibleIndex = 0;

                    dt = ReportDAO.ExecuteQuery("SELECT * FROM [242_Shift]");
                    gridView1.ViewCaption = ReportConstants.ReportName.ShiftGetAll;
                    break;

                case ReportConstants.ReportId.WorkName:

                    gridView1.OptionsBehavior.Editable = true;
                    GridColumn colId = new GridColumn()
                        {
                            Caption = String.Format("ID"),
                            FieldName = "ID",
                        };
                    gridView1.Columns.Add(colId);
                    colId.VisibleIndex = -1;

                    GridColumn colWorkId = new GridColumn()
                        {
                            Caption = String.Format("WorkID"),
                            FieldName = "WorkID",
                        };
                    gridView1.Columns.Add(colWorkId);
                    colWorkId.VisibleIndex = 0;

                    GridColumn colVNName = new GridColumn()
                        {
                            Caption = String.Format("Vietnamese"),
                            FieldName = "Vietnamese",
                        };
                    gridView1.Columns.Add(colVNName);
                    colVNName.VisibleIndex = 1;

                    GridColumn colJapanese = new GridColumn()
                        {
                            Caption = String.Format("Japanese"),
                            FieldName = "Japanese",
                        };
                    gridView1.Columns.Add(colJapanese);
                    colJapanese.VisibleIndex = 2;


                    GridColumn colOrderVn = new GridColumn()
                        {
                            Caption = String.Format("OrderVN"),
                            FieldName = "OrderVN",
                        };
                    gridView1.Columns.Add(colOrderVn);
                    colOrderVn.VisibleIndex = 3;

                    GridColumn colOrderJp = new GridColumn()
                        {
                            Caption = String.Format("OrderJP"),
                            FieldName = "OrderJP",

                        };

                    gridView1.Columns.Add(colOrderJp);
                    colOrderJp.VisibleIndex = 4;

                    gridView1.Columns["OrderJP"].OptionsColumn.AllowEdit = false;
                    gridView1.Columns["OrderVN"].OptionsColumn.AllowEdit = false;

                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.WorkNameGetAll);
                    gridView1.ViewCaption = ReportConstants.ReportName.WorkNameGetAll;
                    //gridView1.UpdateCurrentRow()
                    break;

                default:
                    break;
            }

            gridControl1.DataSource = dt;
            //gridView1.PopulateColumns();
            gridView1.BestFitColumns();
            gridView1.SelectRow(0);
            gridView1.FocusedRowHandle = 0;
        }

        //Export to excel button

        protected virtual void InitPopupMenu()
        {
            menu = new DXPopupMenu();
            menu.Items.Add(new DXMenuItem("Export all", new EventHandler(OnItemClickExportAll), null, null, null, null));
            menu.Items.Add(new DXMenuItem("Export selected row(s)", new EventHandler(OnItemClickExportRows), null, null, null, null));

        }
        void OnItemClickExportAll(object sender, EventArgs e)
        {
            Utilities.ExportToExcel(gridView1, 1, true);
        }
        void OnItemClickExportRows(object sender, EventArgs e)
        {
            Utilities.ExportToExcel(gridView1, 0, true);
        }

        private void gridView1_ValidateRow(object sender, DevExpress.XtraGrid.Views.Base.ValidateRowEventArgs e)
        {
            GridView view = sender as GridView;
            switch (ReportId)
            {
                case ReportConstants.ReportId.WorkName:

                    //Kiểm tra đây là dòng dữ liệu mới hay cũ, nếu là mới thì mình insert
                    if (view.IsNewItemRow(e.RowHandle))
                    {
                        //e.RowHandle trả về giá trị int là thứ tự của dòng hiện tại

                    }
                    //Cũ thì update
                    else
                    {
                        string update = "update [242_WorkName] set WorkID=N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["WorkID"]).ToString() + "', Vietnamese =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Vietnamese"]).ToString() + "', Japanese =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Japanese"]).ToString() + "', [OrderVN] ="
                        + view.GetRowCellValue(e.RowHandle, view.Columns["OrderVN"]).ToString() + ", [OrderJP] ="
                       + view.GetRowCellValue(e.RowHandle, view.Columns["OrderJP"]).ToString() + " where [ID] ="
                       + view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString();
                        ReportDAO.ExecuteNonQuery(update);

                    }
                    break;

                case ReportConstants.ReportId.Shift:

                    //Kiểm tra đây là dòng dữ liệu mới hay cũ, nếu là mới thì mình insert
                    if (view.IsNewItemRow(e.RowHandle))
                    {
                        //e.RowHandle trả về giá trị int là thứ tự của dòng hiện tại

                    }
                    //Cũ thì update
                    else
                    {
                        string update = "update [242_Shift] set [Shift]=N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Shift"]).ToString() + "' where [ID] ="
                       + view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString();
                        ReportDAO.ExecuteNonQuery(update);

                    }
                    break;
            }
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            if (gridView1.SelectedRowsCount > 0)
            {
                var row = new DataRow[gridView1.SelectedRowsCount];
                var workNameId = new string[gridView1.SelectedRowsCount];
                string delete = "";
                switch (ReportId)
                {
                    case ReportConstants.ReportId.WorkName:
                        delete = "delete from [242_WorkName] where ID=";
                        break;
                    case ReportConstants.ReportId.Shift:
                        delete = "delete from [242_Shift] where ID=";
                        break;
                    default:
                        return;
                        break;
                }


                for (int i = 0; i < gridView1.SelectedRowsCount; i++)
                {
                    row[i] = gridView1.GetDataRow(gridView1.GetSelectedRows()[i]);
                    workNameId[i] = row[i]["ID"].ToString();
                }


                if (MessageBox.Show(ReportConstants.Message.DeleteQuestion, "Warning",
                                MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
                {
                    for (int i = 0; i < workNameId.Length; i++)
                    {
                        string sql = delete + workNameId[i];
                        if (ReportDAO.ExecuteNonQuery(sql) > 0)
                        {
                            // _action = 2;
                        }
                        else
                        {
                            MessageBox.Show(ReportConstants.Message.DeleteFailed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }

                    BindDataToGridview(ReportId);

                }
            }
            else
            {
                MessageBox.Show(ToolConstants.Message.TableIsNull, ToolConstants.Form.MessageBox.Message, MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }
        }

        private void btImport_Click(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    switch (ReportId)
                    {
                        case ReportConstants.ReportId.WorkName:
                            SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("WorkID", "WorkID");
                            bulkInsert.ColumnMappings.Add(date);

                            SqlBulkCopyColumnMapping vnName = new SqlBulkCopyColumnMapping("Vietnamese", "Vietnamese");
                            bulkInsert.ColumnMappings.Add(vnName);

                            SqlBulkCopyColumnMapping jpname = new SqlBulkCopyColumnMapping("Japanese", "Japanese");
                            bulkInsert.ColumnMappings.Add(jpname);

                            SqlBulkCopyColumnMapping orderVn = new SqlBulkCopyColumnMapping("OrderVN", "OrderVN");
                            bulkInsert.ColumnMappings.Add(orderVn);

                            SqlBulkCopyColumnMapping orderJp = new SqlBulkCopyColumnMapping("OrderJP", "OrderJP");
                            bulkInsert.ColumnMappings.Add(orderJp);

                            bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_WorkName]";
                            bulkInsert.WriteToServer(dr);

                            break;

                        case ReportConstants.ReportId.Shift:
                            SqlBulkCopyColumnMapping shift = new SqlBulkCopyColumnMapping("Shift", "Shift");
                            bulkInsert.ColumnMappings.Add(shift);

                            bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_Shift]";
                            bulkInsert.WriteToServer(dr);

                            break;
                    }



                    connection.Close();
                    MessageBox.Show("Import data successfully");
                    BindDataToGridview(ReportId);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }


        protected override void WndProc(ref Message m)
        {
            if (m.Msg == 522)     /* WM_MOUSEWHEEL */
            {
                return;
            }

            base.WndProc(ref m);
        }


    }
}
